package com.chen.TestDealNullSiwftCode;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;

import org.apache.commons.lang3.StringUtils;


/**
 *数据库增删改查
 *@author：email: <a href="cyb@ewppay.com"> Administrator </a> 
 *@Date 2017-2-10 下午3:17:27 
 */

public class DBTest {
	private String url = "jdbc:mysql://192.168.20.230:3306/ecainet?characterEncoding=UTF-8&zeroDateTimeBehavior=round";
	private String username = "root";
	private String password = "ecainet";
	public DBTest(String url,String username,String password){
		this.url = url;
		this.username = username;
		this.password = password;
		
		conn = getConn();
	}
	
	Connection conn = null;
	
	/**
	 * 
	 * @Description:  批量处理优化
	 * @param    
	 * @return void  
	 * @throws
	 * @author cyb
	 * @date 2017-2-13
	 */
	public List<NullSwiftCode> dealNullSwiftCodeList2(){
		long start = System.currentTimeMillis();
		List<NullSwiftCode> result = new ArrayList<NullSwiftCode>();
		String sql = "select "
				+ " id," + 
			"	bank_number bankNumber "+ 
			" from "+ 
			"	ev_account_bank_card "+ 
			" where "+ 
			"	swift_code is null "
			 + " limit 500";//查询部分数据
			
			PreparedStatement pstmt;
			try {
				pstmt = conn.prepareStatement(sql);
				ResultSet rs = pstmt.executeQuery();
				while(rs.next()){
					Integer id = rs.getInt("id");
					String bankNumber = rs.getString("bankNumber");
					String swiftCode = getSwiftCode2(bankNumber);
					if(StringUtils.isBlank(swiftCode)){
						swiftCode = "wrong";
					}
					NullSwiftCode entity = new NullSwiftCode();
					entity.setId(id);
					entity.setSwiftCode(swiftCode);
					result.add(entity);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			long end = System.currentTimeMillis();
			return result;
	}
	
	
	private  Integer updateBankCodeById2(List<NullSwiftCode> list){
		long start = System.currentTimeMillis();
		StringBuilder sb = new StringBuilder(" UPDATE ev_account_bank_card set swift_code = case id ");
		if(list != null && list.size() > 0){
			for(NullSwiftCode entity : list){
				sb.append(" when " + entity.getId() + " then '" + entity.getSwiftCode() + "'");
			}
		}
		sb.append(" end  where id in (");
		if(list != null && list.size() > 0){
			for(int i = 0; i < list.size(); i++){
				NullSwiftCode entity = list.get(i);
				if(i != list.size() - 1){
					sb.append(entity.getId() + ",");
				}else{
					sb.append(entity.getId() + ")");
				}
				
			}
		}
		
		 
		int i = 0;
		PreparedStatement pstmt;
		try {
			pstmt = conn.prepareStatement(sb.toString());
			i = pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		long end = System.currentTimeMillis();
		System.out.println("update cost= "+ (end - start));
		return i;
	}
	
	public  void dealNullSwiftCodeList(){
		String sql = "select "
			+ " id," + 
		"	bank_number bankNumber "+ 
		" from "+ 
		"	ev_account_bank_card "+ 
		" where "+ 
		"	swift_code is null";
		
		PreparedStatement pstmt;
		try {
			pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			while(rs.next()){
				Integer id = rs.getInt("id");
				String bankNumber = rs.getString("bankNumber");
				System.out.println("id="+id+",bankNumber="+bankNumber);
//				String swiftCode = getSwiftCode(bankNumber);
//				if(StringUtils.isNotBlank(swiftCode)){
//					updateBankCodeById(swiftCode,id);
//				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public static void main(String[] args){
//		long start = System.currentTimeMillis();
//		String url = "jdbc:mysql://192.168.20.230:3306/ecainet?characterEncoding=UTF-8&zeroDateTimeBehavior=round";
//		 String username = "root";
//		 String password = "ecainet";
//		DBTest t = new DBTest(url,username,password);
//		List<NullSwiftCode> resoures = t.dealNullSwiftCodeList2();
//		System.out.println("query cost = " + (System.currentTimeMillis() - start) + " ms!");
//		
	     ExecutorService fixedThreadPool = Executors.newFixedThreadPool(5); 
	     fixedThreadPool.execute(new Runnable(){
	    	 long start = System.currentTimeMillis();
			@Override
			public void run() {
				String url = "jdbc:mysql://192.168.20.230:3306/ecainet?characterEncoding=UTF-8&zeroDateTimeBehavior=round";
				 String username = "root";
				 String password = "ecainet";
				DBTest t = new DBTest(url,username,password);
				List<NullSwiftCode> resoures = t.dealNullSwiftCodeList2();
				while(resoures != null && resoures.size() > 0){
					t.updateBankCodeById2(resoures);
					resoures = t.dealNullSwiftCodeList2();
				}
				System.out.println("finally cost = " + (System.currentTimeMillis() - start) + " ms!");
			}
	    	 
	     });
	     fixedThreadPool.shutdown();
		
	}
	
	private  Connection getConn(){
		System.out.println(url);
		Connection conn = ConnectionUtil.getConnection(this.url,this.username,this.password);
		return conn;
	}
	
	private  Integer updateBankCodeById(String swiftCode,Integer id){
		String sql = "UPDATE "
			+ " ev_account_bank_card" + 
		" SET "+ 
		"	swift_code = "+ swiftCode +
		" where "+ 
		"	id =" + id;
		 
		int i = 0;
		PreparedStatement pstmt;
		try {
			pstmt = conn.prepareStatement(sql);
			i = pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return i;
	}
	
	private  String findBankCodeByBinArray(List<String> bins){
		String sql = "select "
			+ " bank_code" + 
		" from "+ 
		"	ev_bank_code_bin "+ 
		" where "+ 
		"	bank_bin in( " ;
		for(String bin : bins){
			sql += "'" + bin + "'";
		}
		sql += ") and status = 0 limit 1";
		
		PreparedStatement pstmt;
		try {
			pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			while(rs.next()){
				String bankCode = rs.getString("bank_code");
				return bankCode;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	private  String findBankCodeByBin(String bin){
		String sql = "select "
			+ " bank_code" + 
		" from "+ 
		"	ev_bank_code_bin "+ 
		" where "+ 
		"	bank_bin = " + bin +" and status = 0";
		
		PreparedStatement pstmt;
		try {
			pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			while(rs.next()){
				String bankCode = rs.getString("bank_code");
				return bankCode;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	/**
	 * 
	 * @Description: 优化取swiftCode:根据卡号取卡bin，卡bin再取行号的方法慢
	 * @param @param bankNumber
	 * @param @return   
	 * @return String  
	 * @throws
	 * @author cyb
	 * @date 2017-2-14
	 */
	public  String getSwiftCode2(String bankNumber){
		if(StringUtils.isBlank(bankNumber) || bankNumber.length() < 16){
			return "";
		}
		
		// 卡bin一般取前6位，但也有3位,5位，8位，10位的
		//卡bin 10位的可能前6位跟取6位的一样
		String swiftCode = "";
		List<String> bins = new ArrayList<String>();
		int binMaxLength = 10;
		int binMinLength = 3;
		for(int n = binMaxLength; n >= binMinLength;n--){
			String bin = bankNumber.substring(0,n);
			bins.add(bin);
		}
		swiftCode = findBankCodeByBinArray(bins);
		return swiftCode;
	}
	
	public  String getSwiftCode(String bankNumber){
		long start = System.currentTimeMillis();
		if(StringUtils.isBlank(bankNumber) || bankNumber.length() < 16){
			return "";
		}
		
		// 卡bin一般取前6位，但也有3位,5位，8位，10位的
		//卡bin 10位的可能前6位跟取6位的一样
		String swiftCode = "";
		String bin = "";
		int binMaxLength = 10;
		int binMinLength = 3;
		for(int n = binMaxLength; n >= binMinLength;n--){
			bin = bankNumber.substring(0,n);
			swiftCode = findBankCodeByBin(bin);
			if(StringUtils.isNotBlank(swiftCode)){
				return swiftCode;
			}
		}
		long end = System.currentTimeMillis();
		return swiftCode;
	}
}
